Release 10.1A: OpenEdge Data Management:
SQL Reference

CREATE TRIGGER

Creates a trigger for the specified table. A trigger is a special type of automatically executed stored procedure that helps ensure referential integrity for a database.

Triggers contain Java source code that can use SQL Java classes to carry out database operations. Triggers are automatically activated when an INSERT, UPDATE, or DELETE statement changes the trigger’s target table. The Java source code details what actions the trigger takes when it is activated.

Syntax

CREATE TRIGGER [ owner_name.]trigname { BEFORE | AFTER } { INSERT | DELETE | UPDATE [ OF ( column_name [ , ... ] ) ] } ON table_name [ REFERENCING { OLDROW [ ,NEWROW ] | NEWROW [ ,OLDROW ] } ] [ FOR EACH { ROW | STATEMENT } ] [ IMPORT java_import_clause ] BEGIN java_snippet END

owner_name

Specifies the owner of the trigger. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

trigname

Names the trigger. DROP TRIGGER statements specify the trigger name defined here. SQL also uses trigname in the name of the Java class that it creates from the Java snippet.

BEFORE | AFTER

Denotes the trigger action time. The trigger action time specifies whether the triggered action, implemented by java_snippet, executes BEFORE or AFTER the invoking INSERT, UPDATE, or DELETE statement.

INSERT |DELETE |UPDATE [ OF column_name [ , ...] ] 

Denotes the trigger event. The trigger event is the statement that activates the trigger.

If UPDATE is the triggering statement, this clause can include an optional column list. Only updates to any of the specified columns will activate the trigger. If UPDATE is the triggering statement and does not include the optional column list, then any UPDATE on the table will activate the trigger.

ON table_name

Identifies the name of the table where the trigger is defined. A triggering statement that specifies table_name causes the trigger to execute. table_name cannot be the name of a view.

REFERENCING OLDROW [ , NEWROW ] | NEWROW [ , OLDROW ]

Provides a mechanism for SQL to pass row values as input parameters to the stored procedure implemented by java_snippet. The code in java_snippet uses the getValue method of the NEWROW and OLDROW objects to retrieve values of columns in rows affected by the trigger event and store them in procedure variables. This clause is allowed only if the trigger specifies the FOR EACH ROW clause.

The meaning of the OLDROW and NEWROW arguments of the REFERENCING clause depends on whether the trigger event is INSERT, UPDATE, or DELETE:

INSERT...REFERENCING NEWROW means the triggered action can access values of columns of each row inserted. SQL passes the column values specified by the INSERT statement.
INSERT...REFERENCING OLDROW is meaningless, since there are no existing values for a row being inserted. INSERT...REFERENCING OLDROW generates a syntax error.
UPDATE...REFERENCING OLDROW means the triggered action can access the values of columns, before they are changed, of each row updated. SQL passes the column values of the row as it exists in the database before the update operation.
DELETE...REFERENCING OLDROW means the triggered action can access values of columns of each row deleted. SQL passes the column values of the row as it exists in the database before the delete operation.
DELETE...REFERENCING NEWROW is meaningless, since there are no new existing values to pass for a row being deleted. DELETE...REFERENCING OLDROW generates a syntax error.
UPDATE is the only triggering statement that allows both NEWROW and OLDROW in the REFERENCING clause.
UPDATE...REFERENCING NEWROW means the triggered action can access the values of columns, after they are changed, of each row updated. SQL passes the column values specified by the UPDATE statement.
The trigger action time (BEFORE or AFTER) does not affect the meaning of the REFERENCING clause. For instance, BEFORE UPDATE...REFERENCING NEWROW still means the values of columns after they are updated will be available to the triggered action.
The REFERENCING clause generates an error if the trigger does not include the FOR EACH ROW clause.

FOR EACH { ROW | STATEMENT }

Controls the execution frequency of the triggered action implemented by java_snippet.

FOR EACH ROW means the triggered action executes once for each row being updated by the triggering statement. CREATE TRIGGER must include the FOR EACH ROW clause if it also includes a REFERENCING clause.

FOR EACH STATEMENT means the triggered action executes only once for the whole triggering statement. FOR EACH STATEMENT is the default.

IMPORT java_import_clause

Specifies standard Java classes to import. The IMPORT keyword must be uppercase and on a separate line.

BEGIN
   java_snippet
END

Denotes the body of the trigger or the triggered action. The body contains the Java source code that implements the actions to be completed when a triggering statement specifies the target table. The Java statements become a method in a class that SQL creates and submits to the Java compiler.

The BEGIN and END keywords must be uppercase and on separate lines.

Notes

Triggers can take action on their own table so that they invoke themselves. SQL limits such recursion to five levels.
You can you have multiple triggers on the same table. Multiple UPDATE triggers on the same table must specify different columns. SQL executes all triggers applicable to a given combination of table, trigger event, and action time.
The actions carried out by a trigger can fire another trigger. When this happens, the other trigger’s actions execute before the rest of the first trigger finishes executing.
If a constraint and trigger are both invoked by a particular SQL statement, SQL checks constraints first, so any data modification that violates a constraint does not also fire a trigger.
To modify an existing trigger, you must delete it and issue another CREATE TRIGGER statement. You can query the systrigger system table for information about the trigger before you delete it.

The following example illustrates an UPDATE trigger on a table called BUG_INFO. If the STATUS or PRIORITY fields are modified, the trigger modifies the BUG_SUMMARY and BUG_STATUS tables appropriately, based on defined conditions:

Example

The following code segment illustrates how to use the CREATE TRIGGER statement:

CREATE TRIGGER TRG_TEST04 BEFORE INSERT ON tst_trg_01 REFERENCING NEWROW FOR EACH ROW IMPORT import java.sql.*; BEGIN //Inserting Into tst_trg_03 Integer new_value=newInteger(0); new_value=(Integer)NEWROW.getValue (1,INTEGER); SQLIStatement insert_tst3=new SQLIStatement ("INSERT INTO tst_trg_03 values (?)"); insert_tst3.setParam (1,new_value); insert_tst3.execute(); END

The following code segment illustrates how to set values for a new row in the CREATE TRIGGER statement:

CREATE TRIGGER trg1403 BEFORE INSERT ON tbl1401 REFERENCING NEWROW FOR EACH ROW IMPORT import java.sql.* ; BEGIN INTEGER n2 = new INTEGER(12345); NEWROW.setValue(2, n2); END

Authorization

Must have the DBA privilege or RESOURCE privilege

Related statements

DROP TRIGGER

Note: For more information on creating and using triggers, see OpenEdge Data Management: SQL Development .